drop table safari_freeboard;
select from safari_freeboard;
create table safari_freeboard(
	no number primary key,
	id varchar2(50) not null,
	title varchar2(50) not null,
	content varchar2(3000) not null,
	hits number default 0,
	time_posted date not null,
	ref number default 0,
	restep number default 0,
	relevel number default 0,
	--멤버 아이디 게시판 id 와 연결
	constraint fk1_safari_free_id foreign key(id) references safari_member(id)
)
	--코멘트(덧글) 게시판과 연결
	constraint fk1_safari_free_comment foreign key(ref) references safari_freeboard_comment(ref)
-- comment column 쓰면 부적합한 식별자 라고 뜸... 예약어 인가봄.... content로 수정함
create table safari_freeboard_comment(
	no number primary key,
	id varchar2(50) not null,
	content varchar2(3000) not null,
	time_posted date not null,
	comf number default 0,
	comstep number default 0
)
	
drop table safari_freeboard_comment
select * from safari_freeboard_comment

drop table safari_freeboard
create sequence free_comment_seq nocache;

insert into safari_member(id,password,name,address,birthday,gender,cellphone,email,grade,point) 
values('1234','1234','한명기','용인',19900908,'male','01049381326','to1249@nate.com','customer',5000);

create sequence free_seq nocache;

-- freeboard 게시판 글 등록
insert into safari_freeboard(no,id,title,content,time_posted) values(free_seq.nextval,'1234','첫번쨰글','첫번쨰글내용',sysdate)
select count(*) from safari_freeboard;
-- freeboard 게시판 글 업데이트
update safari_freeboard set title='첫번쨰글 타이틀 수정',  content='첫번째글 내용 수정' where no=1
-- freeboard 게시판 글 삭제
delete safari_freeboard where no=#{no}

-- 게시판 글 상세정보 보기
select id,

-- 게시판 글 페이지 그룹별 보기
		select no,id, title,
		content, hits,time_posted, ref, restep, relevel from (
		select no,id, title,
		content, hits,time_posted, ref, restep, relevel,
		ceil(rownum/10) as page from (
		select no,id, title,
		content, hits,to_char(time_posted,'YYYY.MM.DD')as time_posted, ref, restep, relevel from safari_freeboard
		order by ref desc, restep asc
		)
		)where page=#{value}
		
-- 게시물 글 몇개인지 보기
		select count(no) from safari_freeboard
		select count(*) from safari_freeboard
		
		select * from safari_freeboard
		
		select count(no) from safari_freeboard_comment where comf=146
		select * from safari_freeboard_comment where comf=146
		select * from safari_freeboard_comment where comf=147
		
		
			no number primary key,
	id varchar2(50) not null,
	content varchar2(3000) not null,
	time_posted date not null,
	comf number default 0,
	comstep number default 0
		
		select no, id, content,
		time_posted, comf, comstep from (
		select no, id, content,
		time_posted, comf, comstep,
		ceil(rownum/10) as page from (
		select no, id, content,
		time_posted, comf, comstep  from safari_freeboard_comment
		order by no asc
		)
		)where page=1
		
		select * from safari_freeboard
		select count(*) from safari_freeboard;
		select count(*) from safari_freeboard where title like '123' or content like '12'
		
		
		-- ※ 참고 SQL Query문 (실행 안하셔도 됩니다!)
-- 회원 탈퇴시에 회원 테이블을 참조하는 테이블에 데이터가 있을때, 회원탈퇴가 불가능하다. 
-- 회원탈퇴를 가능하게 하기위해서 하단의 alter문을 사용했었다.
-- union을 사용해서 컬럼명이 같은 3개의 테이블을 합쳐보았다.

-- (1) safari_sellboard : 외래키 제약조건 제거
alter table safari_freeboard drop constraint fk1_safari_free_id;

-- (2) safari_sellboard : 외래키 제약조건 추가
alter table safari_freeboard 
add constraint fk_safari_member_id_free foreign key(id) 
references safari_member(id) 
on delete cascade;

 select no, id, content,
		time_posted, comf, comstep from (
		select no, id, content,
		time_posted, comf, comstep,
		ceil(rownum/10) as page from (
		select no, id, content,
		time_posted, comf, comstep  from safari_freeboard_comment where comf=154
		order by no asc 
		)
		)where page=1
		
		 update safari_freeboard set hits=hits+1 where no=1